﻿using DBUtil.Attributes;
using DotNetCommon.Extensions;
using NUnit.Framework;
using Shouldly;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;

namespace Test.MySql.JsonTests.AnyAllTests.simple
{
    [TestFixture]
    internal class ArrayTestsSimple : TestBase
    {
        #region model
        [Table("test")]
        public class Person
        {
            [PrimaryKey, Identity]
            [Column("id")]
            public int Id { get; set; }

            [JsonMap]
            public string[] Names { get; set; }

            [JsonMap]
            public double[] Scores { get; set; }
        }
        #endregion

        [SetUp]
        public void SetUp()
        {
            DropTableIfExist("test");
            db.ExecuteSql("create table test(id int primary key auto_increment,Scores json,Names json)");
            db.ExecuteSql("""
                insert into test(Scores,Names) values
                    ('[86,95.5,76.25,60]','["jack","tom","lisa"]'),
                    ('[86,100,76.25,40]','["jack","tom2","lisa2"]'),
                    ('[86,95.5,76.25,60]','[]');
                """);
        }

        [Test]
        public void TestAny1()
        {
            var select = db.Select<Person>().Where(i => i.Names.Any());
            var sql = select.ToSql();
            sql.ShouldBe("""
                select t.`id` `Id`,t.`Names`,t.`Scores`
                from `test` t
                where json_length(t.`Names`)>0;
                """);
            var json = select.ToList().ToJson();
            json.ShouldBe("""[{"Id":1,"Names":["jack","tom","lisa"],"Scores":[86,95.5,76.25,60]},{"Id":2,"Names":["jack","tom2","lisa2"],"Scores":[86,100,76.25,40]}]""");
        }

        [Test]
        public void TestAny2()
        {
            var select = db.Select<Person>().Where(i => i.Scores.Any(i => i < 60));
            var sql = select.ToSql();
            sql.ShouldBe("""
                select t.`id` `Id`,t.`Names`,t.`Scores`
                from `test` t
                where 0<(select 1 from json_table(t.`Scores`,'$[*]' columns(`i` decimal(30,15) path '$')) t where t.`i` < 60 limit 1);
                """);
            var json = select.ToList().ToJson();
            json.ShouldBe("""[{"Id":2,"Names":["jack","tom2","lisa2"],"Scores":[86,100,76.25,40]}]""");
        }
        [Test]
        public void TestAny3()
        {
            var select = db.Select<Person>().Where(i => i.Names.Any(i => i.StartsWith("jack")));
            var sql = select.ToSql();
            sql.ShouldBe("""
                select t.`id` `Id`,t.`Names`,t.`Scores`
                from `test` t
                where 0<(select 1 from json_table(t.`Names`,'$[*]' columns(`i` text path '$')) t where t.`i` like 'jack%' limit 1);
                """);
            var json = select.ToList().ToJson();
            json.ShouldBe("""[{"Id":1,"Names":["jack","tom","lisa"],"Scores":[86,95.5,76.25,60]},{"Id":2,"Names":["jack","tom2","lisa2"],"Scores":[86,100,76.25,40]}]""");
        }

        [Test]
        public void TestAll1()
        {
            var select = db.Select<Person>().Where(i => i.Names.All(i => i.IsNotNullOrWhiteSpace()));
            var sql = select.ToSql();
            sql.ShouldBe("""
                select t.`id` `Id`,t.`Names`,t.`Scores`
                from `test` t
                where json_length(t.`Names`)=(select count(1) from json_table(t.`Names`,'$[*]' columns(`i` text path '$')) t where not (t.`i` is null or t.`i` = ''));
                """);
            var json = select.ToList().ToJson();
            //最后一个 Names:[] 也能够被遍历出来, 因为它是集合为空, 并不是 集合内的元素为null, 可以尝试:
            //new List<string>().All(i => i.IsNotNullOrWhiteSpace()) // 这个得到的也是true
            json.ShouldBe("""[{"Id":1,"Names":["jack","tom","lisa"],"Scores":[86,95.5,76.25,60]},{"Id":2,"Names":["jack","tom2","lisa2"],"Scores":[86,100,76.25,40]},{"Id":3,"Names":[],"Scores":[86,95.5,76.25,60]}]""");
        }

        [Test]
        public void TestAll2()
        {
            var select = db.Select<Person>().Where(i => i.Scores.All(i => i > 50));
            var sql = select.ToSql();
            sql.ShouldBe("""
                select t.`id` `Id`,t.`Names`,t.`Scores`
                from `test` t
                where json_length(t.`Scores`)=(select count(1) from json_table(t.`Scores`,'$[*]' columns(`i` decimal(30,15) path '$')) t where t.`i` > 50);
                """);
            var json = select.ToList().ToJson();
            json.ShouldBe("""[{"Id":1,"Names":["jack","tom","lisa"],"Scores":[86,95.5,76.25,60]},{"Id":3,"Names":[],"Scores":[86,95.5,76.25,60]}]""");
        }
    }
}
